#!/usr/bin/env bash

dt="`date -d "$d  0 days ago "  "+%Y-%m-%d"`"
mysql -uroot -proot -hhadoop2<<EOF
use sqlserver;
select substr(X.transaction_date,1,13),X.station_id,X.mop_name,count(X.item_quantity),sum(X.item_value)
from
(select Z.transaction_date,Y.station_id,Y.mop_name,Y.item_value,Y.item_quantity
from realtime.transactions_vip as Z
join realtime.transaction_items_mop_vip as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id and Y.item_value > 0 )AS X
group by substr(X.transaction_date,1,13),X.station_id,X.mop_name;
EOF